CreateQueryDef Method Example

This example uses the CreateQueryDef method to create and execute both a temporary and a permanent QueryDef. The GetrstTemp function is required for this procedure to run.

Sub CreateQueryDefX()

   Dim dbsNorthwind As Database
   Dim qdfTemp As QueryDef
   Dim qdfNew As QueryDef

   Set dbsNorthwind = OpenDatabase("Northwind.mdb")

   With dbsNorthwind
      ' Create temporary QueryDef.
      Set qdfTemp = .CreateQueryDef("", _
         "SELECT * FROM Employees")
      ' Open Recordset and print report.
      GetrstTemp qdfTemp
      ' Create permanent QueryDef.
      Set qdfNew = .CreateQueryDef("NewQueryDef", _
         "SELECT * FROM Categories")
      ' Open Recordset and print report.
      GetrstTemp qdfNew
      ' Delete new QueryDef because this is a demonstration.
      .QueryDefs.Delete qdfNew.Name
      .Close
   End With

End Sub

Function GetrstTemp(qdfTemp As QueryDef)

   Dim rstTemp As Recordset

   With qdfTemp
      Debug.Print .Name
      Debug.Print "  " & .SQL
      ' Open Recordset from QueryDef.
      Set rstTemp = .OpenRecordset(dbOpenSnapshot)

      With rstTemp
         ' Populate Recordset and print number of records.
         .MoveLast
         Debug.Print "  Number of records = " & _
            .RecordCount
         Debug.Print
         .Close
      End With

   End With

End Function